import Foundation
import SQLite3

// SQLite数据库管理类
class DatabaseManager {
    private var db: OpaquePointer?
    private let dbPath: String
    private let dbQueue = DispatchQueue(label: "database.queue", qos: .utility)
    
    init() {
        // 数据库文件路径 - 存储在用户根目录的.vdh文件夹下
        let vdhPath = NSHomeDirectory() + "/.vdh"
        try? FileManager.default.createDirectory(atPath: vdhPath, withIntermediateDirectories: true)
        self.dbPath = vdhPath + "/video_downloader.db"
        
        openDatabase()
        createTables()
    }
    
    deinit {
        closeDatabase()
    }
    
        // 生成12位随机数ID
    private func generateTaskId() -> String {
        let digits = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
        return String((0..<12).map { _ in digits.randomElement()! })
    }
    
    private func openDatabase() {
        let result = sqlite3_open(dbPath, &db)
        if result != SQLITE_OK {
            print("❌ Unable to open database: \(String(cString: sqlite3_errmsg(db)))")
            return
        }
        print("✅ Database opened successfully at: \(dbPath)")
    }
    
    private func closeDatabase() {
        if sqlite3_close(db) != SQLITE_OK {
            print("❌ Error closing database")
        }
        db = nil
    }
    
    private func createTables() {
        let createTasksTableSQL = """
            CREATE TABLE IF NOT EXISTS tasks (
                id TEXT PRIMARY KEY,
                url TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'pending',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                started_at DATETIME,
                completed_at DATETIME,
                error_message TEXT,
                file_path TEXT
            );
        """
        
        if sqlite3_exec(db, createTasksTableSQL, nil, nil, nil) == SQLITE_OK {
            print("✅ Tasks table created successfully")
        } else {
            print("❌ Failed to create tasks table: \(String(cString: sqlite3_errmsg(db)))")
        }
        
        // 创建索引以提高查询性能
        let createIndexSQL = """
            CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
            CREATE INDEX IF NOT EXISTS idx_tasks_created_at ON tasks(created_at);
        """
        
        if sqlite3_exec(db, createIndexSQL, nil, nil, nil) == SQLITE_OK {
            print("✅ Database indexes created successfully")
        } else {
            print("❌ Failed to create indexes: \(String(cString: sqlite3_errmsg(db)))")
        }
    }
    
    // 添加新任务
    func addTask(url: String) -> String? {
        return dbQueue.sync {
            let taskId = generateTaskId()
            let insertSQL = "INSERT INTO tasks (id, url, status) VALUES (?, ?, ?)"
            var statement: OpaquePointer?
            
            defer {
                sqlite3_finalize(statement)
            }
            
            if sqlite3_prepare_v2(db, insertSQL, -1, &statement, nil) == SQLITE_OK {
                let taskIdCString = taskId.cString(using: .utf8)!
                let urlCString = url.cString(using: .utf8)!
                let statusCString = TaskStatus.pending.rawValue.cString(using: .utf8)!
                
                sqlite3_bind_text(statement, 1, taskIdCString, -1, nil)
                sqlite3_bind_text(statement, 2, urlCString, -1, nil)
                sqlite3_bind_text(statement, 3, statusCString, -1, nil)
                
                if sqlite3_step(statement) == SQLITE_DONE {
                    print("✅ Task added to database with ID: \(taskId), URL: \(url), Status: pending")
                    return taskId
                } else {
                    print("❌ Failed to insert task: \(String(cString: sqlite3_errmsg(db)))")
                }
            } else {
                print("❌ Failed to prepare insert statement: \(String(cString: sqlite3_errmsg(db)))")
            }
            
            return nil
        }
    }
    
    // 更新任务状态
    func updateTaskStatus(id: String, status: TaskStatus, errorMessage: String? = nil, filePath: String? = nil) -> Bool {
        print("🔍 updateTaskStatus called: id=\(id), status=\(status.rawValue), errorMessage=\(errorMessage ?? "nil"), filePath=\(filePath ?? "nil")")
        return dbQueue.sync {
            var updateSQL = "UPDATE tasks SET status = ?"
            
            switch status {
            case .downloading:
                updateSQL += ", started_at = CURRENT_TIMESTAMP"
            case .completed, .failed, .cancelled:
                updateSQL += ", completed_at = CURRENT_TIMESTAMP"
            default:
                break
            }
            
            if errorMessage != nil {
                updateSQL += ", error_message = ?"
            }
            
            if filePath != nil {
                updateSQL += ", file_path = ?"
            }
            
            updateSQL += " WHERE id = ?"
            
            print("🔍 Generated SQL: \(updateSQL)")
            
            var statement: OpaquePointer?
            
            defer {
                sqlite3_finalize(statement)
            }
            
            if sqlite3_prepare_v2(db, updateSQL, -1, &statement, nil) == SQLITE_OK {
                var bindIndex: Int32 = 1
                
                let statusCString = status.rawValue.cString(using: .utf8)!
                sqlite3_bind_text(statement, bindIndex, statusCString, -1, nil)
                bindIndex += 1
                print("🔍 Bound status: \(status.rawValue) at index \(bindIndex - 1)")
                
                if let error = errorMessage {
                    let errorCString = error.cString(using: .utf8)!
                    sqlite3_bind_text(statement, bindIndex, errorCString, -1, nil)
                    bindIndex += 1
                    print("🔍 Bound error: \(error) at index \(bindIndex - 1)")
                }
                
                if let path = filePath {
                    let pathCString = path.cString(using: .utf8)!
                    sqlite3_bind_text(statement, bindIndex, pathCString, -1, nil)
                    bindIndex += 1
                    print("🔍 Bound filePath: \(path) at index \(bindIndex - 1)")
                }
                
                let idCString = id.cString(using: .utf8)!
                sqlite3_bind_text(statement, bindIndex, idCString, -1, nil)
                print("🔍 Bound id: \(id) at index \(bindIndex)")
                
                if sqlite3_step(statement) == SQLITE_DONE {
                    print("✅ SQL update executed successfully for task \(id)")
                    return true
                } else {
                    print("❌ SQL update failed for task \(id): \(String(cString: sqlite3_errmsg(db)))")
                    return false
                }
            }
            
            return false
        }
    }
    
    // 获取任务详情
    func getTask(id: String) -> DownloadTask? {
        return dbQueue.sync {
            let selectSQL = """
                SELECT id, url, status, created_at, started_at, completed_at, error_message, file_path
                FROM tasks WHERE id = ?
            """
            var statement: OpaquePointer?
            
            defer {
                sqlite3_finalize(statement)
            }
            
            if sqlite3_prepare_v2(db, selectSQL, -1, &statement, nil) == SQLITE_OK {
                let idCString = id.cString(using: .utf8)!
                sqlite3_bind_text(statement, 1, idCString, -1, nil)
                
                if sqlite3_step(statement) == SQLITE_ROW {
                    return parseTaskFromStatement(statement)
                } else {
                    print("❌ No task found with ID: \(id)")
                }
            } else {
                print("❌ Failed to prepare select statement: \(String(cString: sqlite3_errmsg(db)))")
            }
            
            return nil
        }
    }
    
    // 获取所有任务列表
    func getAllTasks(status: TaskStatus? = nil, limit: Int = 100) -> [DownloadTask] {
        return dbQueue.sync {
            var selectSQL = """
                SELECT id, url, status, created_at, started_at, completed_at, error_message, file_path
                FROM tasks
            """
            
            if status != nil {
                selectSQL += " WHERE status = ?"
            }
            
            selectSQL += " ORDER BY created_at DESC LIMIT ?"
            
            var statement: OpaquePointer?
            var tasks: [DownloadTask] = []
            
            defer {
                sqlite3_finalize(statement)
            }
            
            if sqlite3_prepare_v2(db, selectSQL, -1, &statement, nil) == SQLITE_OK {
                var bindIndex: Int32 = 1
                
                if let statusFilter = status {
                    sqlite3_bind_text(statement, bindIndex, statusFilter.rawValue, -1, nil)
                    bindIndex += 1
                }
                
                sqlite3_bind_int(statement, bindIndex, Int32(limit))
                
                while sqlite3_step(statement) == SQLITE_ROW {
                    if let task = parseTaskFromStatement(statement) {
                        tasks.append(task)
                    }
                }
            } else {
                print("❌ Failed to prepare select all statement: \(String(cString: sqlite3_errmsg(db)))")
            }
            
            return tasks
        }
    }
    
    // 获取任务统计信息
    func getTaskStats() -> [TaskStatus: Int] {
        return dbQueue.sync {
            let selectSQL = "SELECT status, COUNT(*) as count FROM tasks GROUP BY status"
            var statement: OpaquePointer?
            var stats: [TaskStatus: Int] = [:]
            
            defer {
                sqlite3_finalize(statement)
            }
            
            if sqlite3_prepare_v2(db, selectSQL, -1, &statement, nil) == SQLITE_OK {
                while sqlite3_step(statement) == SQLITE_ROW {
                    if let statusStr = sqlite3_column_text(statement, 0),
                       let status = TaskStatus(rawValue: String(cString: statusStr)) {
                        let count = Int(sqlite3_column_int(statement, 1))
                        stats[status] = count
                    }
                }
            } else {
                print("❌ Failed to prepare stats statement: \(String(cString: sqlite3_errmsg(db)))")
            }
            
            return stats
        }
    }
    
    // 清理旧任务（可选，删除超过指定天数的已完成任务）
    func cleanupOldTasks(olderThanDays days: Int = 30) -> Int {
        return dbQueue.sync {
            let deleteSQL = """
                DELETE FROM tasks 
                WHERE (status = 'completed' OR status = 'failed') 
                AND created_at < datetime('now', '-\(days) days')
            """
            
            if sqlite3_exec(db, deleteSQL, nil, nil, nil) == SQLITE_OK {
                let deletedCount = Int(sqlite3_changes(db))
                print("✅ Cleaned up \(deletedCount) old tasks")
                return deletedCount
            } else {
                print("❌ Failed to cleanup old tasks: \(String(cString: sqlite3_errmsg(db)))")
                return 0
            }
        }
    }
    
    private func parseTaskFromStatement(_ statement: OpaquePointer?) -> DownloadTask? {
        guard let statement = statement else { return nil }
        
        guard let idCStr = sqlite3_column_text(statement, 0) else { return nil }
        let id = String(cString: idCStr)
        
        guard let urlCStr = sqlite3_column_text(statement, 1) else { return nil }
        let url = String(cString: urlCStr)
        
        guard let statusCStr = sqlite3_column_text(statement, 2),
              let status = TaskStatus(rawValue: String(cString: statusCStr)) else { return nil }
        
        let dateFormatter = DateFormatter()
        dateFormatter.dateFormat = "yyyy-MM-dd HH:mm:ss"
        
        // 创建时间
        var createdAt = Date()
        if let createdCStr = sqlite3_column_text(statement, 3) {
            createdAt = dateFormatter.date(from: String(cString: createdCStr)) ?? Date()
        }
        
        // 开始时间
        var startedAt: Date?
        if let startedCStr = sqlite3_column_text(statement, 4) {
            startedAt = dateFormatter.date(from: String(cString: startedCStr))
        }
        
        // 完成时间
        var completedAt: Date?
        if let completedCStr = sqlite3_column_text(statement, 5) {
            completedAt = dateFormatter.date(from: String(cString: completedCStr))
        }
        
        // 错误信息
        var errorMessage: String?
        if let errorCStr = sqlite3_column_text(statement, 6) {
            errorMessage = String(cString: errorCStr)
        }
        
        // 文件路径
        var filePath: String?
        if let pathCStr = sqlite3_column_text(statement, 7) {
            filePath = String(cString: pathCStr)
        }
        
        return DownloadTask(
            id: id,
            url: url,
            status: status,
            createdAt: createdAt,
            updatedAt: Date(),
            error: errorMessage,
            filePath: filePath
        )
    }
}